package com.aqie.demo.database;

import com.aqie.demo.enter.HkEnter;
import com.aqie.demo.utils.JdbcUtils;
import com.aqie.demo.utils.SnowFlakeUtils;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @Author aqie
 * @Date 2021/12/10 14:08
 * @desc
 */
@Slf4j
public class JdbcCRUDByPreparedStatement {
    public static int insert(HkEnter hkEnter) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            //获取一个数据库连接
            conn = JdbcUtils.getConnection();
            //要执行的SQL命令，SQL中的参数使用?作为占位符
            String sql = "insert into c_hk_enter(id,tenant_id, `name`,`code`,building_num,status,contrast_status,client_ip,server_ip,create_time) values(?,?,?,?,?,?,?,?,?,?)";
            //通过conn对象获取负责执行SQL命令的prepareStatement对象
            st = conn.prepareStatement(sql);
            //为SQL语句中的参数赋值，注意，索引是从1开始的
            st.setString(1, String.valueOf(SnowFlakeUtils.getInstance().nextId()));
            st.setString(2, hkEnter.getTenantId());
            st.setString(3, hkEnter.getName());
            st.setString(4, hkEnter.getCode());
            st.setString(5, hkEnter.getBuildingNum());
            st.setInt(6, hkEnter.getStatus());
            st.setInt(7, hkEnter.getContrastStatus());
            st.setString(8,hkEnter.getClientIp());
            st.setString(9,hkEnter.getServerIp());
            st.setString(10, String.valueOf(hkEnter.getCreateTime()));
            //执行插入操作，executeUpdate方法返回成功的条数
            int num = st.executeUpdate();
            return num;

        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            //SQL执行完成之后释放相关资源
            JdbcUtils.release(conn, st, rs);
        }
        return -1;
    }

    public static List<HkEnter> queryAll() {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            //获取一个数据库连接
            conn = JdbcUtils.getConnection();
            //要执行的SQL命令，SQL中的参数使用?作为占位符
            String sql = "select * from c_hk_enter where TO_DAYS(create_time) = TO_DAYS(NOW())";
            //通过conn对象获取负责执行SQL命令的prepareStatement对象
            st = conn.prepareStatement(sql);
            List<HkEnter> hkEnterList = new ArrayList<>();
            rs = st.executeQuery();
            // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            while (rs.next()) {
                HkEnter hkEnter = new HkEnter();
                hkEnter.setId(rs.getLong("id"));
                hkEnter.setName(rs.getString("name"));
                hkEnter.setTenantId(rs.getString("tenant_id"));
                hkEnter.setCode(rs.getString("code"));
                hkEnter.setBuildingNum(rs.getString("building_num"));
                hkEnter.setStatus(rs.getByte("status"));
                hkEnter.setContrastStatus(rs.getByte("contrast_status"));
                hkEnter.setCreateTime(rs.getLong("create_time"));
                hkEnterList.add(hkEnter);
            }
            if(hkEnterList.size() == 0){
                log.info("暂无数据");
            } else {
                log.info("数据量 {} {} ", hkEnterList.size(), hkEnterList);
            }
            return hkEnterList;


        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            //SQL执行完成之后释放相关资源
            JdbcUtils.release(conn, st, rs);
        }
        return Collections.emptyList();
    }

}
